package com.william.jdbcplus.core.util;

import com.william.jdbcplus.common.entity.Entity;
import com.william.jdbcplus.common.enums.IdType;
import com.william.jdbcplus.core.dialect.ddl.DDLUtil;
import com.william.jdbcplus.core.enums.SqlMethod;
import com.william.jdbcplus.core.meta.TableColumnInfo;
import com.william.jdbcplus.core.meta.TableHelperUtil;
import com.william.jdbcplus.core.meta.TableInfo;
import com.william.jdbcplus.core.query.EntityWrap;
import com.william.jdbcplus.core.toolkit.ForFileUtils;
import com.william.jdbcplus.core.toolkit.MapTransformUtils;
import com.william.jdbcplus.core.toolkit.json.JsonUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 备份与还原通用类
 *
 * @author willieni
 */
public class CrudUtil {

    public static <M extends Entity> List<M> listAll(Class<M> clazz, DataSource ds) {
        SqlMethod sqlMethod = SqlMethod.SELECT_LIST;
        EntityWrap<M> wrap = new EntityWrap<>(clazz);
        String sqlSelectAll = TableHelperUtil.getSelectAllColumnNoTable(clazz);
        String sql = String.format(sqlMethod.getSql(), sqlSelectAll, wrap.getTableName(), wrap.getExpression().getWhereBehind());
        Map<String, Object> paramMap = wrap.getParamNameValuePairs();
        List<Map<String, Object>> listMap = getTemplate(ds).queryForList(sql, paramMap);
        List<M> resultRows = MapTransformUtils.mapToEntityList(listMap, wrap.getEntityClass());
        return resultRows;
    }

    /**
     * 根据实体数据源数据还原数据表
     *
     * @param clazz 实体类名
     * @param srcDs 数据源
     * @param dstDs 数据源
     * @param <M>
     * @return
     */
    public static <M extends Entity> int recover(Class<M> clazz, DataSource srcDs, DataSource dstDs) {
        List<M> entityList = listAll(clazz, srcDs);
        int size = recover(entityList, dstDs);
        return size;
    }

    /**
     * 根据实体列表还原数据表
     *
     * @param entityList 实体列表
     * @param ds         数据源
     * @param <M>
     * @return
     */
    public static <M extends Entity> int recover(List<M> entityList, DataSource ds) {
        if (entityList == null || entityList.size() == 0) {
            return 0;
        }

        int max = 10000;
        SqlMethod sqlMethod = SqlMethod.INSERT_LIST;
        M entity = entityList.get(0);
        Class<M> clazz = TableHelperUtil.getEntityClass(entity);
        String tableName = TableHelperUtil.getTableName(clazz);
        TableInfo table = TableHelperUtil.getTableInfo(clazz);
        String columnInsert = TableHelperUtil.getColumnInsert(clazz);
        String batchInsert = TableHelperUtil.getBatchInsert(clazz);
        String sql = String.format(sqlMethod.getSql(), tableName, columnInsert, batchInsert);
        for (int fromIndex = 0; fromIndex < entityList.size(); fromIndex = fromIndex + max) {
            int toIndex = fromIndex + max;
            if (toIndex > entityList.size()) {
                toIndex = entityList.size();
            }

            List<M> rows = entityList.subList(fromIndex, toIndex);
            //对象转 MAP 列表
            List<Map<String, Object>> rowMap = TableHelperUtil.getEntityAttrMapList(rows);

            Connection connection = null;
            PreparedStatement ps = null;
            try {
                connection = ds.getConnection();
                //这里必须设置为false，我们手动批量提交
                connection.setAutoCommit(false);
                //这里需要注意，SQL语句的格式必须是预处理的这种，就是values(?,?,...,?)，否则批处理不起作用
                ps = connection.prepareStatement(sql);
                for (Map<String, Object> map : rowMap) {
                    int parameterIndex = 0;
                    if (table.getIdType() != IdType.AUTO && table.getKeyColumn() != null) {
                        parameterIndex++;
                        TableHelperUtil.setParameterValue(ps, parameterIndex, table.getKeyType(), map.get(table.getKeyProperty()));
                    }

                    for (TableColumnInfo column : table.getColumnList()) {
                        parameterIndex++;
                        TableHelperUtil.setParameterValue(ps, parameterIndex, column.getPropertyType(), map.get(column.getProperty()));
                    }
                    //将要执行的SQL语句先添加进去，不执行
                    ps.addBatch();
                }
                //100W条SQL语句已经添加完成，执行这100W条命令并提交
                ps.executeBatch();
                connection.commit();
            } catch (Exception ex) {

            } finally {
                try {
                    if (null != ps) {
                        ps.close();
                    }
                    if (null != connection) {
                        connection.close();
                    }
                } catch (SQLException ex) {
                }
            }
        }

        return entityList.size();
    }

    /**
     * 根据实体数据源数据还原数据表
     *
     * @param scanPackage 实体包名
     * @param srcDs       数据源
     * @param dstDs       数据源
     * @return
     */
    public static int recover(String scanPackage, DataSource srcDs, DataSource dstDs) {
        List<Class<? extends Entity>> clazzList = DDLUtil.getSortClassList(scanPackage);
        int size = 0;
        for (Class<? extends Entity> clazz : clazzList) {
            List<? extends Entity> entityAll = listAll(clazz, srcDs);
            size = size + recover(entityAll, dstDs);
        }
        return size;
    }

    /**
     * 根据实体数据源数据还原数据表
     *
     * @param path     实体类名
     * @param fileName 数据源
     * @param ds       数据源
     * @return
     */
    public static int recover(String path, String fileName, DataSource ds) {
        String className = fileName.substring(0, fileName.lastIndexOf("."));
        Class<? extends Entity> clazz = null;
        try {
            clazz = (Class<? extends Entity>) Class.forName(className);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String json = ForFileUtils.readFileContent(path, fileName);
        List<? extends Entity> entityList = JsonUtil.json2list(json, clazz);
        int size = recover(entityList, ds);
        return size;
    }

    /**
     * 根据实体数据源数据还原数据表
     *
     * @param path 实体类名
     * @param ds   数据源
     * @return
     */
    public static int recover(String path, DataSource ds) {
        //创建File对象
        File file = new File(path);
        String[] fileNameLists = file.list();
        int size = 0;
        for (String fileName : fileNameLists) {
            size += recover(path, fileName, ds);
        }

        return size;
    }

    /**
     * 备份数据表数据到对应的路径
     *
     * @param clazz
     * @param ds
     * @param path
     * @param <M>
     * @return
     */
    public static <M extends Entity> boolean backupToJson(Class<M> clazz, DataSource ds, String path) {
        List<M> entityList = listAll(clazz, ds);
        String json = JsonUtil.obj2json(entityList);
        String fileName = clazz.getName() + ".json";
        boolean success = ForFileUtils.createAndWrite(path, fileName, json);
        return success;
    }

    /**
     * 备份数据表数据到对应的路径
     *
     * @param scanPackage
     * @param ds
     * @param path
     * @param <M>
     * @return
     */
    public static <M extends Entity> boolean backupToJson(String scanPackage, DataSource ds, String path) {
        List<Class<? extends Entity>> clazzList = DDLUtil.getSortClassList(scanPackage);
        boolean success = false;
        for (Class<? extends Entity> clazz : clazzList) {
            List<? extends Entity> entityAll = listAll(clazz, ds);
            String json = JsonUtil.obj2json(entityAll);
            String fileName = clazz.getName() + ".json";
            success = ForFileUtils.createAndWrite(path, fileName, json);
        }

        return success;
    }

    /**
     * 备份数据表数据到目标数据库
     *
     * @param clazz
     * @param srcDs
     * @param dstDs
     * @param <M>
     * @return
     */
    public static <M extends Entity> int backupToDB(Class<M> clazz, DataSource srcDs, DataSource dstDs) {
        List<M> entityList = listAll(clazz, srcDs);
        int size = recover(entityList, dstDs);
        return size;
    }

    /**
     * 备份数据表数据到目标数据库
     *
     * @param scanPackage
     * @param srcDs
     * @param dstDs
     * @param <M>
     * @return
     */
    public static <M extends Entity> int backupToDB(String scanPackage, DataSource srcDs, DataSource dstDs) {
        List<Class<? extends Entity>> clazzList = DDLUtil.getSortClassList(scanPackage);
        int size = 0;
        for (Class<? extends Entity> clazz : clazzList) {
            List<? extends Entity> entityAll = listAll(clazz, srcDs);
            size = size + recover(entityAll, dstDs);
        }
        return size;
    }

    private static NamedParameterJdbcTemplate getTemplate(DataSource ds) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        return namedParameterJdbcTemplate;
    }
}
